package com.example.demo.demo.jdbc;

import java.sql.*;

/**
 * 使用statement对象执行静态SQL语句
 *
 * @author: htc
 * @date: Created in 15:54 2018/11/2.
 */
public class Demo2 {
    /**
     * jdbc协议:数据库子协议://主机:端口/数据库
     */
    final static String url = "jdbc:postgresql://10.110.91.114/idata_meta?&tcpKeepAlive=true&autoReconnect=true";
    final static String user = "postgres";
    final static String pwd = "mmp&H!^&zHCO8pR";

    public static void main(String[] args) throws Exception {
//        ddl();
//        dmlInsert();
//        dmlUpdate();
//        dmlDelete();
        dqlSelect();
    }

    /**
     * 执行DDL(建表)
     *
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static void ddl() throws ClassNotFoundException, SQLException {
        Connection con = null;
        Statement stmt = null;
        int count = 0;
        try {
            //1、注册驱动程序
            Class.forName("org.postgresql.Driver");

            //2、获取连接对象
            con = DriverManager.getConnection(url, user, pwd);

            //3、创建statement对象
            stmt = con.createStatement();

            //4、准备SQL
            String sql = "DROP TABLE IF EXISTS app.demo;CREATE TABLE app.demo (ID INT, NAME VARCHAR);";

            //5、发送SQL，执行SQL
            count = stmt.executeUpdate(sql);

            System.out.println(count);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //6、释放关闭连接对象(后打开的先关闭)
            if (null != stmt) {
                stmt.close();
            }
            if (null != con) {
                con.close();
            }
        }
    }

    /**
     * DML-INSERT
     *
     * @throws Exception
     */
    public static void dmlInsert() throws Exception {
        System.out.println("############### insert ################");
        Connection con = null;
        Statement stmt = null;
        int count = 0;
        try {
            //1、注册驱动程序
            Class.forName("org.postgresql.Driver");

            //2、获取连接对象
            con = DriverManager.getConnection(url, user, pwd);

            //3、创建statement对象
            stmt = con.createStatement();

            //4、准备SQL
            String sql = "INSERT INTO app.demo(id, name) VALUES (1,'胡廷聪')";

            //5、发送SQL，执行SQL
            count = stmt.executeUpdate(sql);

            System.out.println(count);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //6、释放关闭连接对象(后打开的先关闭)
            if (null != stmt) {
                stmt.close();
            }
            if (null != con) {
                con.close();
            }
        }
    }

    /**
     * DML-UPDATE
     */
    public static void dmlUpdate() throws Exception {
        System.out.println("############### update ################");
        Connection con = null;
        Statement stmt = null;
        int count = 0;
        try {
            //1、注册驱动程序
            Class.forName("org.postgresql.Driver");

            //2、获取连接对象
            con = DriverManager.getConnection(url, user, pwd);

            //3、创建statement对象
            stmt = con.createStatement();

            //4、准备SQL
            String sql = "UPDATE app.demo SET name='廷聪' WHERE id = 1";

            //5、发送SQL，执行SQL
            count = stmt.executeUpdate(sql);

            System.out.println(count);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //6、释放关闭连接对象(后打开的先关闭)
            if (null != stmt) {
                stmt.close();
            }
            if (null != con) {
                con.close();
            }
        }
    }

    /**
     * DML-DELETE
     */
    public static void dmlDelete() throws Exception {
        System.out.println("############### delete ################");
        Connection con = null;
        Statement stmt = null;
        int count = 0;
        try {
            //1、注册驱动程序
            Class.forName("org.postgresql.Driver");

            //2、获取连接对象
            con = DriverManager.getConnection(url, user, pwd);

            //3、创建statement对象
            stmt = con.createStatement();

            //4、准备SQL
            String sql = "DELETE FROM app.demo WHERE id=1";

            //5、发送SQL，执行SQL
            count = stmt.executeUpdate(sql);

            System.out.println(count);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //6、释放关闭连接对象(后打开的先关闭)
            if (null != stmt) {
                stmt.close();
            }
            if (null != con) {
                con.close();
            }
        }
    }

    /**
     * DQL-SELECT
     */
    public static void dqlSelect() throws Exception {
        System.out.println("############### select ################");
        Connection connection = null;
        Statement statement = null;
        ResultSet rs = null;
        try {
            String sql = "select * from app.demo";
            connection = JdbcUtil.getConnection();
            statement = connection.createStatement();
            rs = statement.executeQuery(sql);
            while (rs.next()){
                System.out.print(rs.getString("id"));
                System.out.println(rs.getString("name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.closeResource(connection, statement);
        }

    }
}
